import datetime
import lib.mysql as mysql
import lib.config as config
import lib.util as util


# 获取实时订单
def getMainDataForRide():
    sql = "select DATE_FORMAT(ride_state.created_at, '%Y-%m-%d %H:%i') AS date_format_minute, ride.canton_id, ride.company_id, ride.is_imitate, COUNT(*) num from ride left join ride_state on ride.id = ride_state.ride_id where ride_state.state = 2 and ride_state.created_at between '{}' and '{}' group by ride.canton_id, ride.company_id, ride.is_imitate, date_format_minute".format(start_time, end_time)
    return db.getRows(sql)


# 获取扫码订单
def getMainDataForScanRide():
    sql = "select DATE_FORMAT(created_at, '%Y-%m-%d %H:%i') AS date_format_minute, canton_id, company_id, COUNT(*) as num from scan_ride where is_finished = 1 and created_at between '{}' and '{}' group by canton_id, company_id, date_format_minute".format(start_time, end_time)
    return db.getRows(sql)


# 获取预约无应答
def getMainDataForAppointmentCall():
    sql = "select DATE_FORMAT(created_at, '%Y-%m-%d %H:%i') AS date_format_minute, canton_id, COUNT(*) AS num from appointment_call where state in (0,3) and created_at between '{}' and '{}' group by canton_id, date_format_minute".format(start_time, end_time)
    return db.getRows(sql)


# 获取实时无应答
def getMainDataForRealtimeCall():
    sql = "select DATE_FORMAT(created_at, '%Y-%m-%d %H:%i') AS date_format_minute, canton_id, COUNT(*) AS num from realtime_call where state in (0,3) and driver_id is null and created_at between '{}' and '{}' group by canton_id, date_format_minute".format(start_time, end_time)
    return db.getRows(sql)


# 获取一键叫车
def getMainDataForSimple():
    sql = "select DATE_FORMAT(created_at, '%Y-%m-%d %H:%i') AS date_format_minute, canton_id, company_id, COUNT(*) num from simple_call_ride where category in (0, 2) and created_at between '{}' and '{}' group by canton_id, company_id, date_format_minute".format(start_time, end_time)
    return db.getRows(sql)


# 获取电召
def getMainDataForPhone():
    sql = "select DATE_FORMAT(created_at, '%Y-%m-%d %H:%i') AS date_format_minute, canton_id, company_id, COUNT(*) num from simple_call_ride where category = 1 and created_at between '{}' and '{}' group by canton_id, company_id, date_format_minute".format(start_time, end_time)
    return db.getRows(sql)



def getMainData():
    ride = getMainDataForRide()
    ride = [{'canton_id': b, 'company_id': c, "stats_date": a, 'num': e, 'type': 1, 'is_imitate': d} for a,b,c,d,e in ride]
    scan = getMainDataForScanRide()
    scan = [{'canton_id': b, 'company_id': c, "stats_date": a, 'num': d, 'type': 2, 'is_imitate': 0} for a,b,c,d in scan]
    noa = getMainDataForAppointmentCall()
    noa = [{'canton_id': b, 'company_id': None, "stats_date": a, 'num': c, 'type': 3, 'is_imitate': 0} for a,b,c in noa]
    nor = getMainDataForRealtimeCall()
    nor = [{'canton_id': b, 'company_id': None, "stats_date": a, 'num': c, 'type': 4, 'is_imitate': 0} for a,b,c in nor]
    simple = getMainDataForSimple()
    simple = [{'canton_id': b, 'company_id': c, "stats_date": a, 'num': d, 'type': 5, 'is_imitate': 0} for a,b,c,d in simple]
    phone = getMainDataForSimple()
    phone = [{'canton_id': b, 'company_id': c, "stats_date": a, 'num': d, 'type': 6, 'is_imitate': 0} for a,b,c,d in phone]
    insert_data = [] + ride + scan + noa + nor + simple + phone
    return insert_data


start_time = util.get_start_date()
end_time = util.get_end_date()

current_time = datetime.datetime.now()
if end_time is None:
    # 转为指定格式
    end_time = current_time.strftime("%Y-%m-%d %H:%M:%S")
else:
    end_time = f"{end_time} 23:59:59"

if start_time is None:
    start_time = datetime.datetime.strptime(end_time, "%Y-%m-%d %H:%M:%S") - datetime.timedelta(minutes=10)
    start_time = start_time.strftime("%Y-%m-%d %H:%M:%S")
else:
    start_time = f"{start_time} 00:00:00"
current_time = current_time.strftime("%Y-%m-%d %H:%M:%S")


util.add_log(f"即将处理 {start_time} - {end_time} 的订单数据")

db = mysql.Db(config.config['mysql']['master'])
db1 = mysql.Db(config.config['mysql']['slave'])

call_data = getMainData()
call_size = len(call_data)
util.add_log(f"共有数据 {call_size}")
db1.cursor.execute("START TRANSACTION")
try:
    sql = "delete from dashboard_order_minute where (stats_date BETWEEN '{}' AND '{}') OR stats_date < DATE_SUB(NOW(), INTERVAL 3 MONTH)".format(start_time[:-2]+"00", end_time)
    db1.cursor.execute(sql)
    if call_size > 0:
        sql = "INSERT INTO `dashboard_order_minute` (`canton_id`, `company_id`, `stats_date`, `num`, `type`, `is_imitate`, `created_at`, `updated_at`) VALUES "
        for item in call_data:
            canton_id = item['canton_id'] if item['canton_id'] is not None else 'NULL'
            company_id = item['company_id'] if item['company_id'] is not None else 'NULL'
            sql += f"({canton_id}, {company_id}, '{item['stats_date']}', {item['num']}, {item['type']}, {item['is_imitate']}, '{current_time}', '{current_time}'),"
        sql = sql.strip(',')
        db1.cursor.execute(sql)
    db1.conn.commit()
    util.add_log("处理完成")
except Exception as e:
    db1.conn.rollback()
    util.add_log(f"处理失败: {e}")
